package Dao;

import DBCPUtil.DBCPUtil;
import Dto.Customer;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class CustomerDao {
    QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());

    //列表 分页
    public List<Customer> selectCustomerList(Integer pageNum, String name) throws SQLException {
        String sql = "select * from customer where nickname=? limit ?,?";
        List<Customer> list = qr.query(sql, new BeanListHandler<>(Customer.class), name, (pageNum-1)*10, 10);
        return list;
    }

    //管理员 列表 分页
    public List<Customer> selectSuperCustomerList(Integer pageNum) throws SQLException {
        String sql = "select * from customer limit ?,?";
        List<Customer> list = qr.query(sql, new BeanListHandler<>(Customer.class), (pageNum-1)*10, 10);
        return list;
    }

    //数据条数
    public Long selectCustomerCount() throws SQLException {
        String sql = "select count(*) from customer";
        Long count = qr.query(sql, new ScalarHandler<>());
        return count;
    }

    //客户添加
    public void addCustomer(Customer customer) throws SQLException {
        String sql = "insert into customer (name, company, phone, email, cooperate) values (?, ?, ?, ?, ?)";
        qr.update(sql, customer.getName(), customer.getCompany(), customer.getPhone(), customer.getEmail(), customer.getCooperate());
    }

    //客户预修改
    public Customer preUpdateCustomer(Integer id) throws SQLException {
        String sql = "select * from customer where id = ?";
        Customer customer = qr.query(sql, new BeanHandler<>(Customer.class), id);
        return customer;
    }

    //客户修改
    public void updateCustomer(Customer customer) throws SQLException {
        String sql = "update customer set name=?, company=?, phone=?, email=?, cooperate=? where id=?";
        qr.update(sql, customer.getName(), customer.getCompany(), customer.getPhone(), customer.getEmail(), customer.getCooperate(), customer.getId());
    }

    //客户删除
    public void deleteCustomer(Integer id) throws SQLException {
        String sql = "delete from customer where id=?";
        qr.update(sql, id);
    }

    //对客户名称模糊查询
    public List<Customer> selectByName(String nickname, String name) throws SQLException {
        String sql = "select * from customer where nickname = ? and name like ?";
        List<Customer> list = qr.query(sql, new BeanListHandler<>(Customer.class), nickname, "%" + name + "%");
        return list;
    }

    //对所属公司查询
    public List<Customer> selectByCompany(String nickname, String company) throws SQLException {
        String sql = "select * from customer where nickname = ? and company = ?";
        List<Customer> list = qr.query(sql, new BeanListHandler<>(Customer.class), nickname, company);
        return list;
    }

    //2种全是空 查出所有属于用户的数据
    public List<Customer> selectAllByUser(String nickname) throws SQLException {
        String sql = "select * from customer where nickname=?";
        List<Customer> list = qr.query(sql, new BeanListHandler<>(Customer.class), nickname);
        return list;
    }

    //管理员对客户名称模糊查询
    public List<Customer> selectSuperByName(String name) throws SQLException {
        String sql = "select * from customer where name like ?";
        List<Customer> list = qr.query(sql, new BeanListHandler<>(Customer.class), "%" + name + "%");
        return list;
    }

    //管理员对所属公司查询
    public List<Customer> selectSuperByCompany(String company) throws SQLException {
        String sql = "select * from customer where company = ?";
        List<Customer> list = qr.query(sql, new BeanListHandler<>(Customer.class), company);
        return list;
    }

    //管理员   2种全是空 查出所有属于用户的数据
    public List<Customer> selectSuperAll() throws SQLException {
        String sql = "select * from customer";
        List<Customer> list = qr.query(sql, new BeanListHandler<>(Customer.class));
        return list;
    }
    public List<String> selectFirm() throws SQLException {
        String sql = "select name from customercompany";
        List<String> list = qr.query(sql,new ColumnListHandler<>());
        return list;
    }
}